<%
' +----------------------------------------------------------------------
' | POPASP [ ASP MVC ]
' +----------------------------------------------------------------------
' | Copyright (c) 2016 http://popasp.com All rights reserved.
' +----------------------------------------------------------------------
' | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
' +----------------------------------------------------------------------
' | Author: popasp <1737025626@qq.com>
' +----------------------------------------------------------------------

'在本类中已经使用了Replace作为方法名，所以在本类中不能再使用Replace函数了，请考虑使用正则的Replace方法吧。
Class POPASP_DATABASE_TOOL
	Public conn			'数据库连接对象
	Public version		'版本号
	Public excelConnectMode '连接方式
	Private data_dir		'存放数据库相关文档的目录
	
	' select
	Function [Select] ( ByRef sql ,ByRef parsedOptions , ByRef pageSize )
		On error resume next
		
		dim rs : set rs = getRS( sql )
		
		if not isEmpty( parsedOptions("page") ) Then
		
			if ubound( parsedOptions("page") ) = 0 Then		'如果是一维数组
				rs.pageSize = pageSize
			Else 
				rs.pageSize = parsedOptions("page")(1)				
			End If			
				
			if isNull(parsedOptions("page")(0))  then				
				if isNumeric( Request.QueryString( C_("VAR_PAGE") ) ) then	
					if Request.QueryString( C_("VAR_PAGE") ) > 0 then
						rs.AbsolutePage = Request.QueryString( C_("VAR_PAGE") )
					end if
				end if
			else
				rs.AbsolutePage = parsedOptions("page")(0)
			end if
		End If		

		set [Select] = rs
		call L_( C_("DB_TYPE") & " Select" )
	End Function
	
	' find，返回rs
	Function Find( sql )
		set Find = getRS( sql )
		call L_( C_("DB_TYPE") & " Find" )
	End Function
	
	'获取字符值,如果fields为""，则取field方法fieldRev中的值
	Public Function getField(sql)		
		On Error Resume Next
		dim rs : set rs = getRS( sql )
		rs.movefirst
		if rs.fields.count > 0 then
			getField = rs.Fields(0).Value
		end if
		call closeRS(rs)
		call L_( C_("DB_TYPE") & " getFindSql" )
	End Function
	
	'获取字符值,如果fields为""，则取field方法fieldRev中的值
	Public Function getFields( sql )		
		On Error Resume Next
		dim rs,dict,i,val
		set rs = getRS( sql )
		rs.movefirst
		set dict = D_
		for i = 0 to rs.fields.count-1
			val = rs.Fields(i).Value 
			dict( rs.Fields(i).Name ) = val
		next		
		set getFields = dict
		set dict = nothing
		call closeRS(rs)	
		call L_( C_("DB_TYPE") & " getFindSql" )
	End Function
	
	' 定位查询，获取第N条记录
	Function getN( ByRef rs , ByVal num )
		dim action,i
		if rs.RecordCount < 1 then 
			set getN = D_ : exit Function
		end if
		
		if num >= 0 then
			action = 1 : rs.moveFirst
		else 
			action = -1 : rs.moveLast : num = Abs(num) - 1
		end if
		
		i = 0
		do while not rs.BOF and not rs.EOF
			if i = num then
				exit do
			end if
			if action > 0 then
				rs.MoveNext
			end if
			if action < 0 then
				rs.MovePrevious
			end if
			i = i + 1
		loop
		set getN = getRow(rs)
		closeRS rs
	End Function
	
	' 获取第一条记录
	Function First( ByRef rs )
		rs.moveFirst : set First = getRow(rs) : closeRS rs
	end Function
	
	' 获取最后一条记录
	Function Last(  ByRef rs  )
		rs.moveLast : set Last = getRow(rs) : closeRS rs
	end Function
	
	' 根据rs,得到当前行的内容，并将其转为Dictionary对象
	Function getRow( byref rs )		
		dim cnt,i
		set dict = D_
		cnt = rs.Fields.Count - 1
		for i = 0 to  cnt
			dict( rs.fields(i).name ) = rs.fields(i).value
		next
		set getRow = dict
	end Function
	
	' 分类汇总函数，用来计算Count、Max等
	Function Statistics( sql ,stat )
		On Error Resume Next
		dim rs
		set rs = getRS( sql )
		rs.movefirst
		Statistics = rs("theResult")		
		call closeRS(rs)		
		call L_( C_("DB_TYPE") & " " & stat )	
	End Function
	
	'根据SQL获取结果集
	Function getRS(sql)

		On error resume next
		Dim rs,dict,start
		if not is_empty( C_("SHOW_PAGE_TRACE") ) Then
			start = timer()
		end if		
		
		Set rs = CreateRS() 
		
		With rs
			.ActiveConnection = conn
			.CursorType = 1
			.LockType = 1
		End With
		rs.Source = sql
		rs.Open
		
		if not is_empty( C_("SHOW_PAGE_TRACE") ) Then
			set dict = D_
			dict("time") = FormatNumber((timer() - start)*1000,0,-1,0,0)
			dict("sql") = sql
			set dict("rs") = rs
			POP_MVC.Dict.Push POP_MVC.dSql,"",dict
			Call N_( array("db_query") )
			Call N_( array("recordset") )
		end if		
		
		set getRS = rs		
		if err.number <> 0 Then			
			if Not is_empty(C_("APP_DEBUG")) Then
				call POP_MVC.exit( sql & ";--语句无法执行 ，" & "POPASP_DATABASE_TOOL:168" & " ，原因:" & err.number & "," & err.description )
			end if
		end if
	End Function
	
	' 执行sql语句，适用于update、delete，如果要添加数据，最好使用insert，可以返回最后生成的ID
	' 该函数还不太完善
	Function Execute( sql )
		On Error Resume Next
		Dim cmd,dict,start
		if not is_empty( C_("SHOW_PAGE_TRACE") ) Then
			start = timer()	
		end if

		set cmd = CreateCMD( sql )
		cmd.execute Execute,,129
		set cmd = nothing

		if not is_empty( C_("SHOW_PAGE_TRACE") ) Then
			set dict = D_
			dict("time") = round((timer() - start) * 1000,0)
			dict("sql") = sql
			POP_MVC.Dict.Push POP_MVC.dSql,"",dict
			Call N_( array("db_write") )
		end if
		call L_( C_("DB_TYPE") & " Execute" )
	End Function
	
	' 使用sql向数据表中插入数据，该方法只适用于access数据库，可获取自动增长的ID
	Function InsertBySql( sql , pk )
		On Error Resume Next
		dim rs,key
		
		Set rs=CreateRS() 	'创建结果集对象

		rs.Open sql,conn,1,3
		rs.Update '调用Update方法立即将内存中数据写入数据库中,下面这句是关键的 
		rs.MoveLast '将记录移动最后一条 
		'该函数有待完善
		if not isEmpty(pk) then
			InsertBySql=rs( pk ) '这样就可以立即得到刚才这个新记录的自动编号了 
		else
			InsertBySql = 1
		end if		

		closeRS rs '最后不要忘记关闭对象
		call L_( C_("DB_TYPE") & " InsertBySql" )
	End Function
	
	' 向数据表中插入数据，data为Dictionary对象，其键名与字段名相对应，如果data含主键，须手动删除
	Function InsertByTable(table,data,pk)
		On Error Resume Next
		dim rs,key,bookmark,sql
		
		table = getTable(table)
		Set rs=CreateRS() 	'创建结果集对象

		if isMicroDB() then
			rs.Open table,conn,1,3
		else
			sql = "SELECT * FROM " & table & " WHERE 1=0"
			rs.open sql,conn,1,3
		end if		
		
		rs.AddNew
		
		for each key in data
			rs(key)= data(key)			
		next
		
		if isMicroDB() Then
			bookmark = rs.bookmark
		end if
			
		rs.Update '调用Update方法立即将内存中数据写入数据库中,下面这句是关键的
			
		if isMicroDB() Then
			rs.bookmark = bookmark
		end if
		
		'rs.MoveLast '将记录移动最后一条 
		
		if not isEmpty(pk) and not isEmpty( rs( pk ) ) then
			InsertByTable=rs( pk ) '这样就可以立即得到刚才这个新记录的自动编号了
		else
			InsertByTable = 1
		end if		
		
		closeRS rs '最后不要忘记关闭对象
		call L_( C_("DB_TYPE") & " Insert" )
	End Function
	
	' 更改记录，data为Dictionary对象，其键名与字段名相对应
	Function Update(table,data,where,pk)
		On Error Resume Next
		dim rs,key
		
		table = getTable(table)
		Set rs=CreateRS() 	'创建结果集对象
		rs.Open table,conn,1,3	'假定开始已经创建Conn，并且数据库已经连接		
		rs.filter = where
				
		do while not rs.BOF And Not rs.EOF 
			for each key in data
				if key <> pk then					
					rs(key)= data(key)					
				end if
			next
			rs.moveNext
		loop
		rs.Update '调用Update方法立即将内存中数据写入数据库中,下面这句是关键的 
		Update = rs.RecordCount
		
		closeRS rs '最后不要忘记关闭对象
		call L_( C_("DB_TYPE") & " Update" )
	End Function
	
	' 删除记录，一定要输入where
	Function Delete(table,where)
		On Error Resume Next
		dim rs
		
		table = getTable(table)
		Set rs=CreateRS()	'创建结果集对象
		rs.Open table,conn,1,3	'假定开始已经创建Conn，并且数据库已经连接
		
		rs.filter = where
		Delete = rs.RecordCount		
		rs.delete '调用Update方法立即将内存中数据写入数据库中,下面这句是关键的
		
		closeRS rs '最后不要忘记关闭对象
		call L_( C_("DB_TYPE") & " Delete" )
	End Function
	
	'获取数据表结构
	Public Function getTableStructure( ByRef tableName ,ByRef dTables , ByRef dTS ,ByRef sql)
		dim filename,dict,key,bool
		key = tableName		'dTS中对应表名的键
		
		if isDbType( "access" ) OR isDbType( "sqlite3" ) OR isDbType( "excel" )Then
			filename = POP_MVC.String.rtrim(getDataFileName(tableName),"$")
		end if
		bool = false
		if isObject( dTables ) Then
			if POP_MVC.dict.Exists(dTables,LCase(tableName)) Then
				bool = true
			end if
		end if
		
		if isDbType("excel") Then
			bool = true
		end if

		if bool Then	'如果在所有的表中发现该表
			
			if dTS.Exists( key ) Then				
				set getTableStructure = dTS(key)("fields")
			else
				
				'如果数据表对应的文件不存在
				if file_exists( filename ) and is_empty(C_("APP_DEBUG")) Then
					dTS.add key,file_get_contents( filename )	'从文件中取出数据
					set getTableStructure = dTS(key)("fields")	'只返回字段信息
				Else
					POP_MVC.Dict.Edit dict,"fields",getTableFields( sql )	'字段信息				
					
					if isDbType( "access" ) Then	'如果为access，取第一个类型为3的字段为主键
						POP_MVC.Dict.Edit dict,"prikey",getAccessPrikey( dict("fields") )	'字段信息
					end if
					
					if isDbType( "sqlite3" ) Then	'sqlite3的主键获取较为复杂，需要分析建表语句
						call parseSqlite3Sql(dict,tableName)
					end if
					
					'还可以添加其他信息
					dTS.add key,dict
					set getTableStructure = dict("fields")
					
					call file_put_contents(filename, js_encode(dict) )
				End If				
			end if
		end if
		set dict = nothing
		set fields = nothing
		
		
	End Function
	
	'通过建表语句sql来获取完整的字段信息
	Private Sub parseSqlite3Sql( byref dict,tableName )
		dim sql : sql = "SELECT `sql` FROM `Sqlite_master` WHERE type='table' and name='" & tableName & "' LIMIT 1"
		dim matches,prikey,fields
		sql = getField(sql)		'根据查询语句，获取建表sql语句
		if isEmpty(sql) Then exit Sub	'内置表没有建表语句，得到的sql为Empty，此时退出sub
		sql = POP_MVC.String.reg_replace(sql,"","--.*?$","gm")		'删除每行的注释
		sql = POP_MVC.String.reg_replace(sql,"","/\*.*?\*/","gm")	'删除区块注释。我反复测试，得不到区块注释
		
		'sqlite3的表可以有rowid，也可以没有，有了rowid，才能在字段中设置AUTOINCREMENT
		if NOT POP_MVC.String.reg_test(sql,"without\s+rowid","i") Then	
			dict("rowid") = "rowid"		'字段rowid值也为rowid
			set fields = dict("fields")
			POP_MVC.Dict.Unshift fields,"rowid",3
			set dict("fields") = fields
			if POP_MVC.String.reg_test(sql,"([^ ]+)\s+INTEGER\s+PRIMARY\s+KEY","i") Then
				set matches = POP_MVC.reg.Execute( sql )
				prikey = matches(0).SubMatches(0)
				dict("prikey") = prikey		'获取主键
			else	' 如果不含INTEGER PRIMARY KEY，则主键只能为rowid了
				dict("prikey") = "rowid"
			end if
		else	'如果建表语句含"without rowid"			
			dict("rowid") = ""		'字段rowid为空
			if POP_MVC.String.reg_test(sql,"([^ ]+)\s+INTEGER\s+PRIMARY\s+KEY","i") Then
				set matches = POP_MVC.reg.Execute( sql )
				prikey = matches(0).SubMatches(0)
				dict("prikey") = prikey		'获取主键
			else	' 这个判断多余，因为含without rowid时，要求必须有INTEGER PRIMARY KEY
				dict("prikey") = ""
			end if			
		end if
	End Sub
	
	' 获取主键，如果分配了主键，则取该值，否则返回第一个字段
	Function getPrikey( byref tableName,byref dTS )
		dim key
		if isDbType("access") Then
			getPrikey = getAccessPrikey( dTS(tableName)("fields") )
		end if
		
		if isDbType("sqlite3") Then
			getPrikey = dTS(tableName)("prikey")
		end if
	End Function
	
	Function getAccessPrikey(  byref ts )
		dim key
		for each key in ts
			if ts(key) = 3 Then
				getAccessPrikey = key
			end If				
			Exit Function
		next
	End Function
	
	Public Function getDataFileName( byval str )
		getDataFileName = C_("DB_TYPE") & "_" & mid(md5(POP_MVC.realPath( C_("DB_PATH") )),1,10) & "_" & LCase(str)
	End Function
	
	'初始连接数据库
	Public Sub initConn
		if is_empty(conn) Then				
			call connectDB( getConnStr(C_("DB_PATH"),C_("DB_PWD")) )
		End If
	End Sub
	
	'根据连接字符串连接数据库
	Sub connectDB( conn_str )
		Err.clear
		on error resume next
		Set conn = Server.CreateObject("ADODB.Connection")
		'conn.CursorLocation = 3
		conn.open conn_str
		If Err.number <> 0 Then
			call POP_MVC.exit( "数据库连接失败，失败原因为:" & Err.Number & "，" & err.description )
		End If
	End Sub
	
	'切换数据库
	Sub SwitchDB( dbPath, password )
		if not isEmpty(conn) then
			conn.close
			set conn = nothing
		end if
		call connectDB( getConnStr( dbPath, password ) )	
	End Sub
	
	Function getConnStr( dbPath, password )
		dim ext,tDb		
		If Instr(dbPath,":")>0 Then : tDb = dbPath : Else : tDb = POP_MVC.realPath(dbPath) : End If

		if isDbType("access") Then
			call HandlerDbFileNotExists(dbPath)
			ext = getFileExt(dbPath)
			if ext = "mdb" Then				
				getConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & tDb & ";Jet OLEDB:Database Password="& password & ";"
			elseif ext = "accdb" Then
				getConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Share Exclusive;Data Source=" & tDb & ";"
			End if		
		Elseif isDbType("sqlite3") Then
			call HandlerDbFileNotExists(dbPath)
			getConnStr = "DRIVER={SQLite3 ODBC Driver};Database=" & tDb & ";"	
		Elseif isDbType("excel") Then			
			call HandlerDbFileNotExists(dbPath)
			'IMEX的值，0为只写，1为只读，2为可写可读
			'HDR表示要把第一行作为数据还是作为列名,作为数据用HDR=no,作为列名用HDR=yes;
			'Provider=Microsoft.Jet.Oledb.4.0;data source=D:\\Data.xls;Extended Properties=Excel 8.0;
			if not is_empty( excelConnectMode ) then	' 查询用
				if POP_MVC.String.iEqual( POP_MVC.String.rstr( tDb , ".") , ".xls" ) Then
					getConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & tDb & ";Extended Properties='Excel 8.0;HDR=yes;IMEX=2';"
				elseif POP_MVC.String.iEqual( POP_MVC.String.rstr( tDb , ".") , ".xlsx" ) Then
					getConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & tDb & ";Extended Properties='Excel 12.0;HDR=yes;IMEX=2';"
				end if
			else	'遍历表名用
				if POP_MVC.String.iEqual( POP_MVC.String.rstr( tDb , ".") , ".xls" ) Then
					getConnStr = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & tDb & ";ReadOnly=true;Provider=MSDASQL" 
				elseif POP_MVC.String.iEqual( POP_MVC.String.rstr( tDb , ".") , ".xlsx" ) Then
					getConnStr = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & tDb & ";ReadOnly=true;Provider=MSDASQL" 
				end if				
			end if
		End if				
	End Function
	
	'处理数据库文件不存在时的情况
	Sub HandlerDbFileNotExists( ByVal dbPath )
		if Not POP_MVC.file.isFile( dbPath ) Then
			if is_localhost() Then
				Call POP_MVC.exit( "数据库文件“" & dbPath & "”不存在" )
			else
				Call POP_MVC.exit( "数据库文件不存在" )
			end if
		End if			
	End Sub

	
	' 删除数据
	Function Remove( ByRef sql,ByRef parsedOptions )		
		On Error Resume Next
		dim cmd		
		if ( not isEmpty( parsedOptions("where") )) Then		
			set cmd = CreateCMD(sql)
			cmd.execute Remove,,129			
			set cmd = nothing
		else 
			call trigger_error("Remove")
		end If
		if err.number<> 0 then
			call POP_MVC.exit( sql & ";--语句无法执行 ，" & "POPASP_DATABASE_TOOL:487" & " ，原因:" & err.number & "," & err.description )
		end if
		call L_( C_("DB_TYPE") & " Remove" )
	End Function
	
	' 修改数据
	Public Function Save(sql)		
		err.clear
		On Error Resume Next
		dim cmd
		set cmd = CreateCMD(sql)
		cmd.execute Save,,129
		set cmd = nothing
		if err.number <> 0 Then
			if Not is_empty(C_("APP_DEBUG")) Then
				call POP_MVC.exit( sql & ";--语句无法执行 ，" & "POPASP_DATABASE_TOOL:520" & " ，原因:" & err.number & "," & err.description)
			end if
		end if
		call L_( C_("DB_TYPE") & " Save" )
	End Function
	
	'DELETE FROM `post` WHERE post_id>10
	Function getDeleteSql( popts )
		dim sql		
		sql = "DELETE FROM "
		if (  not isEmpty( popts("table") ) ) Then
			sql = sql & "`" & popts("table") & "`"
		else 
			call trigger_error("getDeleteSql")
		end If
		
		if ( not isEmpty(popts("where")) ) Then
			sql = sql & " WHERE " & popts("where")
			getDeleteSql = sql	
		else 
			call trigger_error("getDeleteSql")
		end If
	End Function
	
	'select top 1 arr(0) * from tb Where id not in(select top (arr(1) - 1) * arr(0) id from tb Where 其它条件 order by 排序字段 [DESC]) and 其它条件
	Function getSelectSql( byref popts , byref tableName , byref fields )
		dim sql,key,item,is_micro_db
		
		is_micro_db = isMicroDB()
		
		if ( not isEmpty( popts("unionAll") ) ) Then 
			sql = ""
			for each key in popts("unionAll")
				sql = sql & " UNION ALL " & popts("unionAll")(key)
			next
			sql = mid(sql,11)
		elseif ( not isEmpty( popts("union") ) ) Then 
			sql = ""
			for each key in popts("union")
				sql = sql & " UNION " & popts("union")(key)
			next
			sql = mid(sql,7)
		else
			sql = "SELECT "
			if is_micro_db Then
				if not isEmpty( popts("top") ) then
					sql = sql & "TOP " & popts("top") & " "
				end if
			End If
			
			if is_empty( popts("field") ) then
				sql = sql & " * FROM "
			else
				sql = sql & popts("field") & " FROM "
			end if
			
			if (  not isEmpty( popts("table") ) AND  popts("table") <> "" ) Then
				if isDbType("excel") Then
					sql = sql & getTable(popts("table"))
				else
					sql = sql & "`" & popts("table") & "` "
				end if
			elseif (  not isEmpty( tableName ) AND  tableName <> "" ) Then
				if isDbType("excel") Then
					sql = sql & getTable(tableName)
				else
					sql = sql & "`" & tableName & "` "
				end if				
			else 
				call trigger_error("getSelectSql")
			end If
			
			if ( not isEmpty( popts("leftJoin") )  AND  popts("leftJoin") <> "" ) Then
				sql = sql & " LEFT JOIN " & popts("leftJoin") & " "
			end If	
			
			if ( not isEmpty( popts("where") ) AND  popts("where") <> "") Then sql = sql & " WHERE " & popts("where") & " "		
			if ( not isEmpty( popts("group") ) AND  popts("group") <> "" ) Then sql = sql & " GROUP BY " & popts("group") & " "		
			if ( not isEmpty( popts("having") ) AND  popts("having") <> "" ) Then sql = sql & " HAVING " & popts("having") & " "		
			if ( not isEmpty( popts("order") ) AND  popts("order") <> "" ) Then sql = sql & " ORDER BY " & popts("order") & " "

			' 连接limit字句
			if (not is_micro_db) AND ( not isEmpty( popts("limit") ) ) Then 
				if not isArray( popts("limit") ) Then
					popts("limit") = array( popts("limit") )
				end if
				if ubound( popts("limit") ) > 0 Then			
					sql = sql & " LIMIT " & popts("limit")(0) & " , " & popts("limit")(1) & " "
				else
					sql = sql & " LIMIT " & popts("limit")(0) & " "
				end if
			end if
			
		end if
		getSelectSql = sql
		
	End Function	
	
	' 获取表的所有字段与字段类型
	' Excel表格的字段类型不管怎么设置，获取到的都是200
	Function getTableFields( sql )		
		dim rs,cnt,i		
		set getTableFields = POP_MVC.dict.Create()	
		set rs = getRS( sql )
		cnt = rs.Fields.Count
		for i = 0 to  cnt-1				
			getTableFields.add rs.fields(i).name,rs.fields(i).type
		next
		closeRS rs		
	End Function	
	
	'是否为exp表达式
	function dataIsExp(data)
		dataIsExp = false
		if isArray(data) then		
			if ubound(data) = 1 and data(0) = "exp" then
				dataIsExp = true
			end if
		end if
	end function
	
	function safe( str )
		safe = POP_MVC.String.reg_replace(str,"''","'","g")
	end function
	
	' mode为数据表中对应字段的类型，val为添加或修改的值
	Function getSqlStr(mode,val)
		if isArray( val ) Then 	'如果是数组的话，array("exp","views + 1")，那么不用引号
			if UBound( val ) = 1 Then
				if LCase( val(0) ) = "exp" Then
					getSqlStr = val(1)
				End If
			End If
		Else
			select case mode 
				case 200,201,202,203	'文本与备注					
					getSqlStr = "'" & safe(val) & "'"
				case 7,13,133,135	'日期
					if isMicroDB() then
						getSqlStr = "#" & val & "#"
					else
						getSqlStr = "'" & val & "'"
					end if
				case 2,3,4,5,6,72,131	'数值
					if isNumeric(val) then
						getSqlStr = val
					else
						getSqlStr = "''"
					end if
				case 11	'布尔型
					if CStr(val) = CStr(0) OR LCase(val) = "off" OR LCase(val) = "false" Then
						getSqlStr = False
					else 
						getSqlStr = True
					End if
				Case Else
					getSqlStr = "'" & safe(val)  & "'"
			end select	
		End If
	End Function
	
	'UPDATE `post` SET `title` = '测试3',`add_time` = #2016/6/28 9:57:20#,`is_display` = True WHERE post_id>10
	Public Function getUpdateSql( ByRef popts , ByRef ts )
		dim sql,key	
		'如果表名为空、data为空、where为空，都抛出异常
		if isEmpty( popts("table") ) OR isEmpty( popts("data") ) OR isEmpty( popts("where") ) Then
			call trigger_error("getUpdateSql")
		end if
		
		sql = "UPDATE "
		sql = sql & "`" & popts("table") & "` SET "			
		if dataIsExp(  popts("data") ) then		'如果是exp表达式
			sql = sql & popts("data")(1) & " WHERE " & popts("where")
			getUpdateSql = sql
		elseif POP_MVC.count( popts("data") ) > 0 Then	'Dictionary对象
			if isDbType("excel") then					
				for each key in popts("data")					
					sql = sql & "`" & key  & "`" & " = " & getExcelSqlStr(ts(key),popts("data")(key)) & ","
				next				
			else				
				for each key in popts("data")
					sql = sql & "`" & key  & "`" & " = " & getSqlStr(ts(key),popts("data")(key)) & ","
				next
			end if
			
			sql = mid( sql,1,len(sql)-1 )	'去掉末尾的逗号
			sql = sql & " WHERE " & popts("where")
			getUpdateSql = sql
		else
			call trigger_error("getUpdateSql")
		End If
		set ts = nothing
	End Function
	
	function getExcelSqlStr( ByRef fieldType , byval value )
		if typename(value)="String" AND isNumeric( value ) then
			if  POP_MVC.String.Exists( value , "." ) Then
				value = CDbl(value)
				getExcelSqlStr = getSqlStr( VarType(value) ,value )
			else
				if value >= -32768 AND value<=32767 Then
					value = CInt(value)
				elseif value >= -2147483648 AND value<= 2147483647 Then
					value = CLng(value)
				else
					value = CDbl(value)
				end if
				getExcelSqlStr = getSqlStr( VarType(value) ,value )
			end if
		elseif typename(value)="String" AND  isDate( value ) Then
			value = CDate(value)
			getExcelSqlStr = getSqlStr( VarType(value) ,value )
		else
			getExcelSqlStr = getSqlStr(fieldType,value)
		end if	
		
	End function
	
	'UPDATE `post` SET `title` = '测试3',`add_time` = #2016/6/28 9:57:20#,`is_display` = True WHERE post_id>10
	Public Function getInsertSql( ByRef table ,ByRef data, ByRef ts )
		dim sql,key,temp
		
		'如果表名为空、data为空、where为空，都抛出异常
		if isEmpty( table ) OR isEmpty( data ) Then
			call trigger_error("getInsertSql")
		end if
		
		sql = "INSERT INTO `" & table & "` ( "
		sql = sql & "`" & POP_MVC.Dict.KeyJoin( array(data,"`, `") ) & "`) VALUES ( "
		if dataIsExp(  data ) then		'如果是exp表达式
			sql = sql & data(1) & " ) "
			getInsertSql = sql
		elseif POP_MVC.count( data ) > 0 Then	'Dictionary对象			
			if isDbType("excel") then
				for each key in data
					sql = sql & getExcelSqlStr(ts(key),data(key)) & ","
				next				
			else
				for each key in data
					sql = sql & getSqlStr(ts(key),data(key)) & ","
				next
			end if
			sql = mid( sql,1,len(sql)-1 )	'去掉末尾的逗号
			sql = sql & " ) "
			getInsertSql = sql
		else
			call trigger_error("getInsertSql")
		End If
		set ts = nothing
	End Function
	
	Function obj2arr( obj )
		dim key,i,arr()
		i = 0
		redim preserve arr( POP_MVC.count(obj)-1 )
		for each key in obj
			if isObject( obj(key) ) then
				set arr(i) = obj(key)
			else
				arr(i) = obj(key)
			End If
			i = i+1
		next
		obj2arr = arr
	End Function
	
	' 解析data
	' 传入dict对象： {name:"zhangsan","sex":"男"}
	Public Function ParseData(opts)
		if dataIsExp( opts ) Then
			ParseData = opts
		elseif typeName(opts)="Dictionary" Then
			set ParseData = opts
		Else
			call trigger_error("ParseData")
		End If
	End Function	

	' 解析order
	' 可以传入字符串: id 或者 id desc 或者 name desc,time asc
	' 也可以传入dict对象： {name:"desc","time":asc}
	Public Function ParseOrder(opts)
		dim optsType : optsType = TypeName( opts )
		dim key,str:str=""
		if optsType = "String" Then
			ParseOrder = opts
		elseif isObject(opts) Then
			for each key in opts
				str = str & key & " " & opts(key) & " ,"
			Next
			str = mid( str , 1, len(str)-1 )	
			ParseOrder = str
		Else
			call trigger_error("ParseOrder")
		End If
	End Function
	
	Public Function parseString( opts,method )
		if TypeName( opts ) = "String" Then 
			parseString = opts
		Else 
			Call trigger_error( method )
		End if
	End Function
	
	' 解析table，只能传入字符串
	Public Function ParseTable(opts)
		ParseTable = parseString( opts,"ParseTable" )
		if isDbType("excel") Then
			ParseTable = getExcelTableName(ParseTable)
		end if
	End Function
	
	' 解析group，只能传入字符串
	Public Function ParseGroup(opts)
		ParseGroup = parseString( opts,"ParseGroup" )
	End Function
	
	Public Function parseDictionary( opts , method )
		if TypeName( opts ) = "Dictionary" Then
			set  parseDictionary = opts
		else
			call trigger_error( method )
		End If
	End Function
	
	Public Function ParseUnion( opts )
		set ParseUnion = parseDictionary( opts , "ParseUnion" )
	End Function
	
	Public Function ParseUnionAll( opts )
		set ParseUnionAll = parseDictionary( opts , "ParseUnionAll" )
	End Function
	
	' 解析table，只能传入字符串
	Public Function parseNumeric( opts , method )
		if is_numeric(opts)  Then 
			parseNumeric = CInt( opts )
		Else 
			Call trigger_error(method)
		End if
	End Function
	
	' 解析table，只能传入字符串
	Public Function ParseTop(opts)
		if isMicroDB() Then
			ParseTop = parseNumeric(opts,"ParseTop")
		else
			ParseTop = ParseLimit( opts )
		end if
	End Function
	
	' 解析limit
	' 可以传入整数(5) ，字符串("1,5")，数组( array(1,5) )
	Public Function ParseLimit ( opts )		
		dim optsType : optsType = TypeName( opts )
		dim arr,i,bound	
		if isMicroDB() Then
			ParseLimit = ParseTop(opts)
		else
			if optsType = "Integer"  OR optsType = "Long" OR optsType = "Null" Then
				ParseLimit = array( opts )
			elseif optsType = "String"  Then 				
				arr = split(opts,",")	
				bound = ubound(arr)
				for i = 0 to bound
					arr(i) = trim( arr(i) )
				next
				ParseLimit = arr
			Elseif isArray( opts ) Then
				ParseLimit = opts
			Else 
				Call trigger_error("ParseLimit")
			End if
		end if
	End Function
	
	Public Function ParsePage( opts )
		dim optsType : optsType = TypeName( opts )
		dim arr,i,bound	
		if optsType = "Integer"  OR optsType = "Long" OR optsType = "Null" Then
			ParsePage = array( opts )
		elseif optsType = "String"  Then 				
			arr = split(opts,",")	
			bound = ubound(arr)
			for i = 0 to bound
				arr(i) = trim( arr(i) )
			next
			ParsePage = arr
		Elseif isArray( opts ) Then
			ParsePage = opts
		Else 
			Call trigger_error("ParsePage")
		End if
	End Function
	
	Public Function ParseLeftJoin( opts )
		dim optsType : optsType = TypeName( opts )
		if optsType = "String" Then
			ParseLeftJoin = opts
		Elseif isArray( opts ) Then
			ParseLeftJoin = Join( opts , "," )
		Else 
			call trigger_error( "ParseLeftJoin" )
		End If
	End Function
	
	
	Function ParseField( opts , fields )
		dim key,arr,str
		if opts.Exists("field") then
			if TypeName( opts("field") ) = "String" Then
				ParseField = opts("field")
			Elseif isArray( opts("field") ) Then
				ParseField = Join( opts("field") , " , " )
			Else 			
				call trigger_error("ParseField")
			End If
		elseif opts.Exists("fieldRev") then
			if TypeName( opts("fieldRev") ) = "String" Then
				arr = split( trim(opts("fieldRev")) ,"," )
			Elseif isArray(  opts("fieldRev") ) Then
				arr = opts("fieldRev")
			Else 			
				call trigger_error("ParseFieldRev")
			End If		
			
			for each key in arr
				if fields.Exists(key) then
					call fields.remove(key)
				else
					fields(key) = ""
				end if
			next 
			
			str = ""
			for each key in fields
				str = str & ", " & key
			next			
			str = POP_MVC.ltrim(str,",")
			ParseField = str
		end if
		set fields = nothing
	End Function
	

	' 获得文件的后缀名
	Function getFileExt( file )
		getFileExt = mid(file,inStrRev(file,".")+1)
	End Function
	
	' 从文件中取出json内容
	Function file_get_contents(filename)
		set file_get_contents = js_decode(POP_MVC.file_get_contents( getFilePath(filename) ))		
	End Function
	
	' 判断文件是否存在
	Function file_exists( filename )
		file_exists = POP_MVC.file.isFile( getFilePath(filename) )
	End Function
	
	' 向文件中写入json字符串
	Function file_put_contents(filename,content)
		file_put_contents = POP_MVC.file_put_contents(getFilePath(filename),content)
	End Function
	
	' 取得完整文件路径
	Function getFilePath ( filename )
		getFilePath = data_dir & filename & ".asp"
	End Function
	
	' 设置表名
	Sub setPoptsTable( ByRef popts ,ByRef tableName )	
		on error resume next
		if isEmpty( popts("table")) Then
			if not isEmpty(tableName) Then
				popts("table") = tableName
			else 
				call trigger_error("setPoptsTable")
			End if	
		Else 
			call trigger_error("setPoptsTable")
		end If
		
		if isDbType("excel") Then
			popts("table") = getExcelTableName(popts("table"))
		end if
	End Sub
	
	Private Sub Class_Initialize		
		data_dir = APP_PATH & "/Runtime/Data/"
		version = "1.3"
		excelConnectMode = 1
	End Sub
	
	Private Sub Class_Terminate
		'在这里千万不能注销对象conn，否则会出错，conn的注销在popasp.asp文件中
	End Sub	

	' 如果出错，抛出异常，中止程序运行
	Sub trigger_error( str )
		if str = "" Then
			POP_MVC.ajax C_("DB_TYPE") & " 出错了"
		else 
			POP_MVC.ajax C_("DB_TYPE") & " " & str
		End If
		Response.end
	End Sub
	
	'处理 增/改 中的数据
	Sub handlerData ( ByRef ts, ByRef data )
		dim key
		if typeName(data) = "Dictionary" then
			for each key in data
				if isDbType("excel") Then	'excel数据库时，字段类型均为200					
					if Not ts.Exists( key ) Then	'如果无此字段，则删除
						 call data.remove(key)
					elseif isNull( data(key)  ) Then	'如果为null，也删除
						call data.remove(key)				
					End If				
				else
					if Not ts.Exists( key ) Then	'如果无此字段，则删除
						 call data.remove(key)
					elseif isNull( data(key)  ) Then	'如果为null，也删除
						
						call data.remove(key)
					elseif NOT (ts(key)>=200 AND ts(key)<=203) AND UCase(data(key))="NULL" Then	'如果为在非文本或备注下，为字符串"null"，也删除
						call data.remove(key)
					else
						select case ts(key)
							case 2,3,4,5,6,72,131		'如果为数字型，但不是数值，也删除
								if Not isNumeric( data(key) ) Then data.remove(key)
							case 7,13,133,135			'如果为日期型，但不是日期的有效格式，也删除
								if Not isDate( data(key) ) Then data.remove(key)
							case 11
								if typename( data(key) ) = "String" Then
									if "0" = CStr(data(key)) OR LCase(data(key)) = "off" OR LCase(data(key)) = "false" Then
										data(key) = False
									Else
										data(key) = True
									End If						
								End If						
						End Select					
					End If
				end if
			next
		end if
		Call L_( C_("DB_TYPE") & " handlerData" )
		set ts = nothing
	End Sub
	
	'根据sql创建对象server.createobject("adodb.command")，使用完之后，一定要记得关闭
	Function CreateCMD( sql )
		dim cmd		
		call initConn	'连接数据库
		Set cmd=server.createobject("adodb.command")
		cmd.ActiveConnection = conn
		cmd.CommandText = sql
		set CreateCMD = cmd
	End Function
	
	'创建对象server.createobject("adodb.recordset")，使用完之后，一定要记得关闭
	Function CreateRS()
		call initConn	'连接数据库
		set CreateRs = server.createobject("adodb.recordset") 
	End Function
	
	'关闭recordset
	Sub closeRS( ByRef rs )
		rs.close : set rs = nothing
	End Sub
	
	' 是否为微软数据库
	Private Function isMicroDB( )
		isMicroDB = false
		if isDbType("access") OR isDbType("mssql") OR isDbType("excel") Then
			isMicroDB = true
		end if		
	End Function
	
	Private Function getTable( byval table )
		if isDbType("access") Then
			table = "`" & table & "`"
		elseif isDbType("excel") Then
			table = "`" & getExcelTableName(table) & "`"
		end if
		getTable = table
	End Function
	
	'判断是否为该种数据库
	Function isDbType( mode )
		isDbType = POP_MVC.String.iEqual( C_("DB_TYPE") , mode )
	End Function
	
	Function getExcelTableName( tableName )
		getExcelTableName = POP_MVC.String.rtrim( tableName,"$" ) & "$"
	End Function
End Class
%>